﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Xml;

namespace ABDLight
{
    public class DBAct
    {
        
        public string CleanID { get; set; }
        public int RowGUID { get; set; }
        public string Login { get; set; }
        public string FileName { get; set; }
        public string iqPostCode { get; set; }
        public string iqFullAddress { get; set; }
        public string iqHouse { get; set; }
        public string iqCase { get; set; }
        public string iqBuild { get; set; }
        public string iqLiter { get; set; }
        public string iqVlad { get; set; }
        public string iqBlock { get; set; }
        public string iqPom { get; set; }
        public string iqPav { get; set; }
        public string iqUch { get; set; }
        public string iqObsch { get; set; }
        public string iqFlat { get; set; }
        public string iqKom { get; set; }
        public string iqOffice { get; set; }
        public string iqKab { get; set; }
        public string iqAbon { get; set; }
        public string iqVch { get; set; }
        public string iqKLADR { get; set; }
        public string iqError { get; set; }
        public string iqStatus { get; set; }
        public string iqCountry { get; set; }
        public string iqRegion { get; set; }
        public string iqRegionType { get; set; }
        public string iqSubRegion { get; set; }
        public string iqSubRegionType { get; set; }
        public string iqCity { get; set; }
        public string iqCityType { get; set; }
        public string iqNp { get; set; }
        public string iqNpType { get; set; }
        public string iqStreet { get; set; }
        public string iqStreetType { get; set; }
        public string iqWorkAddress { get; set; }
        public string ISOK { get; set; }
        public List<DBAct> DBActList { get; set; }

        public DBAct()
        {
            Login = "";
            FileName = "";
            iqPostCode = "";
            iqFullAddress = "";
            iqHouse = "";
            iqCase = "";
            iqBuild = "";
            iqLiter = "";
            iqVlad = "";
            iqBlock = "";
            iqPom = "";
            iqPav = "";
            iqUch = "";
            iqObsch = "";
            iqFlat = "";
            iqKom = "";
            iqOffice = "";
            iqKab = "";
            iqAbon = "";
            iqVch = "";
            iqKLADR = "";
            iqError = "";
            iqStatus = "";
            iqCountry = "";
            iqRegion = "";
            iqRegionType = "";
            iqSubRegion = "";
            iqSubRegionType = "";
            iqCity = "";
            iqCityType = "";
            iqNp = "";
            iqNpType = "";
            iqStreet = "";
            iqStreetType = "";
            iqWorkAddress = "";
            ISOK = "";   
            DBActList = new List<DBAct>();
        }


    ///////////
        public static void CreateTable(string CS = "Server=localhost;Integrated Security=SSPI;", string DB = "ABDLight", string Table = "DBAct")
        {
            using (SqlConnection conn = new SqlConnection(CS + " Initial Catalog=" + DB + ";"))
            {
                conn.Open();
                try
                {

                    //string SQLCommand = "DROP TABLE [" + Table + "]";                    
                    //SqlCommand command = new SqlCommand(SQLCommand, conn);
                    //command.ExecuteNonQuery();

                    string SQLCommand1 = "SET ANSI_NULLS ON";
                    SqlCommand command1 = new SqlCommand(SQLCommand1, conn);
                    command1.ExecuteNonQuery();

                    string SQLCommand5 = "SET QUOTED_IDENTIFIER ON";
                        
                    SqlCommand command5 = new SqlCommand(SQLCommand5, conn);
                    command5.ExecuteNonQuery();

                    string SQLCommand2 = "CREATE TABLE [" + Table + "](" +
                    "[c_CleanID] [uniqueidentifier] ROWGUIDCOL  NOT NULL," +
                    "[c_Login] [nvarchar](50) NOT NULL," +
                    "[c_FileName] [nvarchar](max) NOT NULL," +
                    "[c_iqPostCode] [nvarchar](50) NULL," +
                    "[c_iqFullAddress] [nvarchar](max) NULL," +
                    "[c_iqHouse] [nvarchar](10) NULL," +
                    "[c_iqCase] [nvarchar](10) NULL," +
                    "[c_iqBuild] [nvarchar](10) NULL," +
                    "[c_iqLiter] [nvarchar](10) NULL," +
                    "[c_iqVlad] [nvarchar](10) NULL," +
                    "[c_iqBlock] [nvarchar](10) NULL," +
                    "[c_iqPom] [nvarchar](10) NULL," +
                    "[c_iqPav] [nvarchar](10) NULL," +
                    "[c_iqUch] [nvarchar](10) NULL," +
                    "[c_iqObsch] [nvarchar](10) NULL," +
                    "[c_iqFlat] [nvarchar](10) NULL," +
                    "[c_iqKom] [nvarchar](10) NULL," +
                    "[c_iqOffice] [nvarchar](10) NULL," +
                    "[c_iqKab] [nvarchar](10) NULL," +
                    "[c_iqAbon] [nvarchar](10) NULL," +
                    "[c_iqVch] [nvarchar](50) NULL," +
                    "[c_iqKLADR] [nvarchar](50) NULL," +
                    "[c_iqError] [nvarchar](50) NULL," +
                    "[c_iqStatus] [nvarchar](max) NULL," +
                    "[c_iqCountry] [nvarchar](max) NULL," +
                    "[c_iqRegion] [nvarchar](max) NULL," +
                    "[c_iqRegionType] [nvarchar](max) NULL," +
                    "[c_iqSubRegion] [nvarchar](max) NULL," +
                    "[c_iqSubRegionType] [nvarchar](max) NULL," +
                    "[c_iqCity] [nvarchar](max) NULL," +
                    "[c_iqCityType] [nvarchar](max) NULL," +
                    "[c_iqNp] [nvarchar](max) NULL," +
                    "[c_iqNpType] [nvarchar](max) NULL," +
                    "[c_iqStreet] [nvarchar](max) NULL," +
                    "[c_iqStreetType] [nvarchar](max) NULL," +
                    "[c_iqWorkAddress] [nvarchar](max) NULL," +
                    "[c_RowGUID] [int] IDENTITY(1,1) NOT NULL," +
                    "[c_ISOK] [nvarchar](50) NULL," +
                 "CONSTRAINT [PK_" + Table + "] PRIMARY KEY CLUSTERED" +
                "(" +
                    "[c_CleanID] ASC" +
                ")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]" +
                ") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]";

                    SqlCommand command2 = new SqlCommand(SQLCommand2, conn);
                    command2.ExecuteNonQuery();

                    string SQLCommand3 = "ALTER TABLE [" + Table + "] SET (LOCK_ESCALATION = AUTO)";
                    SqlCommand command3 = new SqlCommand(SQLCommand3, conn);
                    command3.ExecuteNonQuery();

                    string SQLCommand4 = "ALTER TABLE [" + Table + "] ADD  CONSTRAINT [DF_" + Table + "_c_CleanID]  DEFAULT (newid()) FOR [c_CleanID]";
                    SqlCommand command4 = new SqlCommand(SQLCommand4, conn);
                    command4.ExecuteNonQuery();

                   
                }
                catch (System.Exception ex)
                {
                    MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }

                }
            }
        }
      

        public static bool CreateDB(string CS = "", string DB = "")
        {
            if (string.IsNullOrEmpty(CS))
            {
                CS = "";
            }
            if (string.IsNullOrEmpty(DB))
            {
                DB = "";
            }

            String str = "CREATE DATABASE "+ DB ;
            SqlConnection myConn = new SqlConnection(CS);
            SqlCommand myCommand = new SqlCommand(str, myConn);

            try
            {
                myConn.Open();
                myConn.ChangeDatabase("master");
                myCommand.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {              
                //MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return false;
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
            }
            return true;
        }

        public static void TruncateTable(string CS = "", string DB = "", String Table = "")
        {
            if (string.IsNullOrEmpty(CS))
            {
                CS = "";
            }
            if (string.IsNullOrEmpty(DB))
            {
                DB = "";
            }
            if (string.IsNullOrEmpty(Table))
            {
                Table = "";
            }

            String str = String.Format("truncate table [{0}].[dbo].[{1}]", DB, Table);
            SqlConnection myConn = new SqlConnection(CS + " Initial Catalog=" + DB + ";");
            SqlCommand myCommand = new SqlCommand(str, myConn);

            try
            {
                myConn.Open();
                myCommand.ExecuteNonQuery();
             
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
            }
        }

        public static void DropTable(string CS = "", string DB = "", String Table = "")
        {
            if (string.IsNullOrEmpty(CS))
            {
                CS = "";
            }
            if (string.IsNullOrEmpty(DB))
            {
                DB = "";
            }
            if (string.IsNullOrEmpty(Table))
            {
                Table = "";
            }

            String str = String.Format("drop table [{0}].[dbo].[{1}]", DB, Table);
            SqlConnection myConn = new SqlConnection(CS);
            SqlCommand myCommand = new SqlCommand(str, myConn);

            try
            {
                myConn.Open();
                myCommand.ExecuteNonQuery();

            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
            }
        }

        public static void DropDB(string CS = "", string DB = "")
        {
            if (string.IsNullOrEmpty(CS))
            {
                CS = "";
            }
            if (string.IsNullOrEmpty(DB))
            {
                DB = "";
            }

            SqlConnection myConn = new SqlConnection(CS);
       
            try
            {
                 myConn.Open();
                string deleteCommand = @"DROP DATABASE [" + DB + "]";
                 SqlCommand deletecommand  = new SqlCommand(deleteCommand, myConn);
                deletecommand.ExecuteNonQuery();
              
               

            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
            }

          
        }

        public static void Insert(string CS = "", string DB = "", string Table = "", Dictionary<String, String> data = null)
        {
            if (string.IsNullOrEmpty(CS))
            {
                CS = "";
            }
            if (string.IsNullOrEmpty(DB))
            {
                DB = "";
            }
            if (string.IsNullOrEmpty(Table))
            {
                Table = "";
            }

            String columns = "";
            String values = "";
            Boolean returnCode = true;

            foreach (KeyValuePair<String, String> val in data)
            {
                columns += String.Format(" {0},", val.Key.ToString());
                values += String.Format(" '{0}',", val.Value);
            }

            columns = columns.Substring(0, columns.Length - 1);
            values = values.Substring(0, values.Length - 1);

            String str = String.Format("insert into {0}({1}) values({2});", Table, columns, values);
            SqlConnection myConn = new SqlConnection(CS + " Initial Catalog=" + DB + ";");
            SqlCommand myCommand = new SqlCommand(str, myConn);

            try
            {
                myConn.Open();
                myCommand.ExecuteNonQuery();

            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
            }
        }

     

        public static void Update(string CS = "", string DB = "", string Table = "", Dictionary<String, String> data = null, string where = "")
        {
            if (string.IsNullOrEmpty(CS))
            {
                CS = "";
            }
            if (string.IsNullOrEmpty(DB))
            {
                DB = "";
            }
            if (string.IsNullOrEmpty(Table))
            {
                Table = "";
            }

            String vals = "";
            Boolean returnCode = true;

            if (data.Count >= 1)
            {
                foreach (KeyValuePair<String, String> val in data)
                {
                    vals += String.Format(" {0} = '{1}',", val.Key.ToString(), val.Value.ToString());
                }
                vals = vals.Substring(0, vals.Length - 1);
            }

            String str = String.Format("update {0} set {1} where {2};", Table, vals, where);
            SqlConnection myConn = new SqlConnection(CS);
            SqlCommand myCommand = new SqlCommand(str, myConn);

            try
            {
                myConn.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
            }
          
        }

        public static void Delete(string CS = "", string DB = "", string Table = "", string where = "")
        {
            if (string.IsNullOrEmpty(CS))
            {
                CS = "";
            }
            if (string.IsNullOrEmpty(DB))
            {
                DB = "";
            }
            if (string.IsNullOrEmpty(Table))
            {
                Table = "";
            }

            String str = String.Format("delete from {0} where {1};", Table, where);
            SqlConnection myConn = new SqlConnection(CS + " Initial Catalog=" + DB + ";");
            SqlCommand myCommand = new SqlCommand(str, myConn);

            try
            {
                myConn.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
            }
        }


        public static void CreateTable(string CS = "", string DB = "", string Table = "", Dictionary<String, String> Columns = null)
        {
            if (string.IsNullOrEmpty(CS))
            {
                CS = "";
            }
            if (string.IsNullOrEmpty(DB))
            {
                DB = "";
            }
            if (string.IsNullOrEmpty(Table))
            {
                Table = "";
            }

            String _Columns = "";
            Boolean returnCode = true;
            if (Columns.Count >= 1)
            {
                foreach (KeyValuePair<String, String> val in Columns)
                {
                    _Columns += String.Format("{0} {1} null,", val.Key.ToString(), val.Value.ToString());
                }
                _Columns = _Columns.Substring(0, _Columns.Length - 1);
            }

            String str = String.Format("CREATE TABLE [{0}](" + "{1}" + ")",Table,_Columns);

            SqlConnection myConn = new SqlConnection(CS + " Initial Catalog=" + DB + ";");
            SqlCommand myCommand = new SqlCommand(str, myConn);

            try
            {
                myConn.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
            }

        }


        // функции для работы с БД
        public static DataTable GetDataTable(string CS = "", string DB = "", string Table = "", string sql = "", string search = "")
        {
            if (string.IsNullOrEmpty(CS))
            {
                CS = "Data Source=srv207;Initial Catalog=tamga;User ID=webuser;Password=#Andromeda2014; Connect Timeout=30";
            }
            if (string.IsNullOrEmpty(DB))
            {
                DB = "";
            }
            if (string.IsNullOrEmpty(Table))
            {
                Table = "";
            }
            if (string.IsNullOrEmpty(sql))
            {
                sql = "exec mq_ois_response " + "'" + search + "'";
            }

            DataTable dt = new DataTable();
            String str = sql;

            SqlConnection myConn = new SqlConnection(CS);
            SqlCommand myCommand = new SqlCommand(str, myConn);

            try
            {
                myConn.Open();
                SqlDataAdapter da = new SqlDataAdapter(myCommand);//SQL запрос
                da.Fill(dt);
            }
            catch (System.Exception ex)
            {
                //MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
            }
            return dt;
        }

      

        public static XmlDocument GetXmlData(string CS = "", string DB = "", string Table = "", string sql = "", string TableXML = "Videos")
        {
            if (string.IsNullOrEmpty(CS))
            {
                CS = "";
            }
            if (string.IsNullOrEmpty(DB))
            {
                DB = "";
            }
            if (string.IsNullOrEmpty(Table))
            {
                Table = "";
            }
            if (string.IsNullOrEmpty(sql))
            {
                sql = "select * from " + "[" + Table + "]";
            }

            DataTable dt = new DataTable();
            DataSet ds = new DataSet();

            String str = sql;

            SqlConnection myConn = new SqlConnection(CS);
            SqlCommand myCommand = new SqlCommand(str, myConn);

            try
            {
                myConn.Open();
                SqlDataAdapter da = new SqlDataAdapter(myCommand);//SQL запрос
                da.Fill(ds, TableXML);

            }
            catch (System.Exception ex)
            {
                //MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
            }
            XmlDocument doc = new XmlDataDocument(ds);
            return doc;
        }

        static string GetData(string search)
        {
            string CS = "Data Source=websearch;Initial Catalog=IQSENG;User ID=Igor;Password=#Andromeda2014;";
            string SqlCmd = "exec [dbo].[spp_getProduct] '" + search + "'";
            string ans = "";
            DataTable dt = GetDataTable(CS, "", "", SqlCmd);

            //string CS = "Data Source=websearch;Initial Catalog=IQSENG;User ID=Igor;Password=#Andromeda2014;";
            //string SqlCmd = "exec [dbo].[spp_getProduct] '" + search + "'";
            //string ans = "";
            //XmlDocument xml = GetXmlData(CS, "", "", SqlCmd, "Product");
            //xml.Save("XML.xml");

            var stop = "1";


            if (dt.Rows.Count > 0)
            {
                try
                {
                    for (int rownum = 0; rownum < dt.Rows.Count; rownum++)
                    {
                        ans += dt.Rows[rownum]["OrgName"].ToString() + "&&" + dt.Rows[rownum]["procent"].ToString() + "||";
                    }
                    ans += "_end";
                    ans = ans.Replace("||_end", "");
                }
                catch (Exception ex)
                {

                }
            }

            return ans;

        }

        // конец блока функций БД


        public static DataTable GetDataTable(string CS = "", string DB = "", string Table = "", string sql = "")
        {
            if (string.IsNullOrEmpty(CS))
            {
                CS = "";
            }
            if (string.IsNullOrEmpty(DB))
            {
                DB = "";
            }
            if (string.IsNullOrEmpty(Table))
            {
                Table = "";
            }
            if (string.IsNullOrEmpty(sql))
            {
                sql = "select * from " + "[" + Table + "]";
            }

            DataTable dt = new DataTable();
            String str = sql;

            SqlConnection myConn = new SqlConnection(CS);           
            SqlCommand myCommand = new SqlCommand(str, myConn);

            try
            {
                myConn.Open();
                SqlDataAdapter da = new SqlDataAdapter(myCommand);//SQL запрос
                da.Fill(dt);               
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
            }
            return dt;
        }



        private static string getOutput(string strInput)
        {
            var regex = new Regex("(.)\\1+");
            strInput = regex.Replace(strInput, "$1"); // remove duplicate characters
            strInput = removeOwels(strInput);
            return (strInput + "&&&&&").ToString().Substring(0, 5);
        }

        private static string removeOwels(string strInput)
        {
            string strOutput = "";
            if (strInput.Length <= 5)
                strOutput = strInput;
            else
            {
                string ProcessedString = strInput.Substring(0, 1);
                for (int vLoop = 1; vLoop < strInput.Length; vLoop++)
                {
                    string NextChar = strInput.Substring(vLoop, 1);
                    if (!((NextChar == "A" || NextChar == "E" || NextChar == "I" || NextChar == "O" || NextChar == "U") && ProcessedString.Length + strInput.Length - vLoop > 5))
                        ProcessedString += NextChar;

                }
                strOutput = ProcessedString;
            }
            return strOutput;
        }


    }
    
}
